


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Vendor_Projects') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Vendor_Projects
GO

create view v_tb_Vendor_Projects as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Vendor_ProjectId] as [Vendor_ProjectId]
	,[VendorFK] as [VendorFK]
	,[Name] as [Name]
	,[ScheduledStartDate] as [ScheduledStartDate]
	,[ActualStartDate] as [ActualStartDate]
	,[ScheduledEndDate] as [ScheduledEndDate]
	,[ActualEndDate] as [ActualEndDate]
	,[ScheduledCost] as [ScheduledCost]
	,[ActualCost] as [ActualCost]
	,[TypeFk] as [TypeFk]
	,[StatusFk] as [StatusFk]
	,[UtilityFk] as [UtilityFk]
	,[ProjectTypeFk] as [ProjectTypeFk]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Name]), 255) as [FriendlyName]
from
	tb_Vendor_Projects
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_Project_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_Project_Test
GO


create proc up_Project_Test
	@Vendor_ProjectId int
as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Vendor_ProjectId] 
	from [tb_Vendor_Projects] source
	where [source].[Vendor_ProjectId] = @Vendor_ProjectId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_Project_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_Project_Select
GO

create proc up_Project_Select
	 @Vendor_ProjectId int = null 
	,@VendorFK int = null 
	,@Name nvarchar(50) = null 
	,@ScheduledStartDate datetime = null 
	,@ActualStartDate datetime = null 
	,@ScheduledEndDate datetime = null 
	,@ActualEndDate datetime = null 
	,@ScheduledCost decimal(19, 2) = null 
	,@ActualCost decimal(19, 2) = null 
	,@TypeFk int = null 
	,@StatusFk int = null 
	,@UtilityFk int = null 
	,@ProjectTypeFk int = null 
as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Vendor_Projects
where
		IsNull([v_tb_Vendor_Projects].[Vendor_ProjectId], -1) = Coalesce(@Vendor_ProjectId, [v_tb_Vendor_Projects].[Vendor_ProjectId], -1)
	AND	IsNull([v_tb_Vendor_Projects].[VendorFK], -1) = Coalesce(@VendorFK, [v_tb_Vendor_Projects].[VendorFK], -1)
	AND	IsNull([v_tb_Vendor_Projects].[Name], '') = Coalesce(@Name, [v_tb_Vendor_Projects].[Name], '')
	AND	IsNull([v_tb_Vendor_Projects].[ScheduledStartDate], '1/1/1900 12:00AM') = Coalesce(@ScheduledStartDate, [v_tb_Vendor_Projects].[ScheduledStartDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ActualStartDate], '1/1/1900 12:00AM') = Coalesce(@ActualStartDate, [v_tb_Vendor_Projects].[ActualStartDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ScheduledEndDate], '1/1/1900 12:00AM') = Coalesce(@ScheduledEndDate, [v_tb_Vendor_Projects].[ScheduledEndDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ActualEndDate], '1/1/1900 12:00AM') = Coalesce(@ActualEndDate, [v_tb_Vendor_Projects].[ActualEndDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ScheduledCost], -1) = Coalesce(@ScheduledCost, [v_tb_Vendor_Projects].[ScheduledCost], -1)
	AND	IsNull([v_tb_Vendor_Projects].[ActualCost], -1) = Coalesce(@ActualCost, [v_tb_Vendor_Projects].[ActualCost], -1)
	AND	IsNull([v_tb_Vendor_Projects].[TypeFk], -1) = Coalesce(@TypeFk, [v_tb_Vendor_Projects].[TypeFk], -1)
	AND	IsNull([v_tb_Vendor_Projects].[StatusFk], -1) = Coalesce(@StatusFk, [v_tb_Vendor_Projects].[StatusFk], -1)
	AND	IsNull([v_tb_Vendor_Projects].[UtilityFk], -1) = Coalesce(@UtilityFk, [v_tb_Vendor_Projects].[UtilityFk], -1)
	AND	IsNull([v_tb_Vendor_Projects].[ProjectTypeFk], -1) = Coalesce(@ProjectTypeFk, [v_tb_Vendor_Projects].[ProjectTypeFk], -1)
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_Project_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_Project_Select_Top1
GO

create proc up_Project_Select_Top1
	 @Vendor_ProjectId int = null 
	,@VendorFK int = null 
	,@Name nvarchar(50) = null 
	,@ScheduledStartDate datetime = null 
	,@ActualStartDate datetime = null 
	,@ScheduledEndDate datetime = null 
	,@ActualEndDate datetime = null 
	,@ScheduledCost decimal(19, 2) = null 
	,@ActualCost decimal(19, 2) = null 
	,@TypeFk int = null 
	,@StatusFk int = null 
	,@UtilityFk int = null 
	,@ProjectTypeFk int = null 
as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Vendor_Projects
where
		IsNull([v_tb_Vendor_Projects].[Vendor_ProjectId], -1) = Coalesce(@Vendor_ProjectId, [v_tb_Vendor_Projects].[Vendor_ProjectId], -1)
	AND	IsNull([v_tb_Vendor_Projects].[VendorFK], -1) = Coalesce(@VendorFK, [v_tb_Vendor_Projects].[VendorFK], -1)
	AND	IsNull([v_tb_Vendor_Projects].[Name], '') = Coalesce(@Name, [v_tb_Vendor_Projects].[Name], '')
	AND	IsNull([v_tb_Vendor_Projects].[ScheduledStartDate], '1/1/1900 12:00AM') = Coalesce(@ScheduledStartDate, [v_tb_Vendor_Projects].[ScheduledStartDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ActualStartDate], '1/1/1900 12:00AM') = Coalesce(@ActualStartDate, [v_tb_Vendor_Projects].[ActualStartDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ScheduledEndDate], '1/1/1900 12:00AM') = Coalesce(@ScheduledEndDate, [v_tb_Vendor_Projects].[ScheduledEndDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ActualEndDate], '1/1/1900 12:00AM') = Coalesce(@ActualEndDate, [v_tb_Vendor_Projects].[ActualEndDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Vendor_Projects].[ScheduledCost], -1) = Coalesce(@ScheduledCost, [v_tb_Vendor_Projects].[ScheduledCost], -1)
	AND	IsNull([v_tb_Vendor_Projects].[ActualCost], -1) = Coalesce(@ActualCost, [v_tb_Vendor_Projects].[ActualCost], -1)
	AND	IsNull([v_tb_Vendor_Projects].[TypeFk], -1) = Coalesce(@TypeFk, [v_tb_Vendor_Projects].[TypeFk], -1)
	AND	IsNull([v_tb_Vendor_Projects].[StatusFk], -1) = Coalesce(@StatusFk, [v_tb_Vendor_Projects].[StatusFk], -1)
	AND	IsNull([v_tb_Vendor_Projects].[UtilityFk], -1) = Coalesce(@UtilityFk, [v_tb_Vendor_Projects].[UtilityFk], -1)
	AND	IsNull([v_tb_Vendor_Projects].[ProjectTypeFk], -1) = Coalesce(@ProjectTypeFk, [v_tb_Vendor_Projects].[ProjectTypeFk], -1)
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_Project_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_Project_Insert
GO

create proc up_Project_Insert
	 @Vendor_ProjectId int =null OUTPUT  
	,@VendorFK int  
	,@Name nvarchar(50)  
	,@ScheduledStartDate datetime  
	,@ActualStartDate datetime = null  
	,@ScheduledEndDate datetime  
	,@ActualEndDate datetime = null  
	,@ScheduledCost decimal(19, 2)  
	,@ActualCost decimal(19, 2) = null  
	,@TypeFk int  
	,@StatusFk int = null  
	,@UtilityFk int  
	,@ProjectTypeFk int  
as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Vendor_Projects 
	(
	
	 [VendorFK] 
	,[Name] 
	,[ScheduledStartDate] 
	,[ActualStartDate] 
	,[ScheduledEndDate] 
	,[ActualEndDate] 
	,[ScheduledCost] 
	,[ActualCost] 
	,[TypeFk] 
	,[StatusFk] 
	,[UtilityFk] 
	,[ProjectTypeFk] 
	)
	values
	(
	
	 @VendorFK 
	,@Name 
	,@ScheduledStartDate 
	,@ActualStartDate 
	,@ScheduledEndDate 
	,@ActualEndDate 
	,@ScheduledCost 
	,@ActualCost 
	,@TypeFk 
	,@StatusFk 
	,@UtilityFk 
	,@ProjectTypeFk 
	)
set @Vendor_ProjectId = scope_identity()
select * from v_tb_Vendor_Projects where Vendor_ProjectId = @Vendor_ProjectId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_Project_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_Project_Update
GO


create proc up_Project_Update
	 @Vendor_ProjectId int  
	,@VendorFK int = null  
	,@Name nvarchar(50) = null  
	,@ScheduledStartDate datetime = null  
	,@ActualStartDate datetime = null  
	,@ScheduledEndDate datetime = null  
	,@ActualEndDate datetime = null  
	,@ScheduledCost decimal(19, 2) = null  
	,@ActualCost decimal(19, 2) = null  
	,@TypeFk int = null  
	,@StatusFk int = null  
	,@UtilityFk int = null  
	,@ProjectTypeFk int = null  
	,@OriginalVendor_ProjectId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalVendor_ProjectId is null
	set @OriginalVendor_ProjectId = @Vendor_ProjectId

-- test
declare @Result bit
execute @Result = up_Project_Test @OriginalVendor_ProjectId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Vendor_ProjectId)
	raiserror(505060, 16, 1, 'Project', @x)
	return
end

-- proceed
update tb_Vendor_Projects 
set

	 [VendorFK] = IsNull(@VendorFK, [source].[VendorFK])
	,[Name] = IsNull(@Name, [source].[Name])
	,[ScheduledStartDate] = IsNull(@ScheduledStartDate, [source].[ScheduledStartDate])
	,[ActualStartDate] =  @ActualStartDate
	,[ScheduledEndDate] = IsNull(@ScheduledEndDate, [source].[ScheduledEndDate])
	,[ActualEndDate] =  @ActualEndDate
	,[ScheduledCost] = IsNull(@ScheduledCost, [source].[ScheduledCost])
	,[ActualCost] =  @ActualCost
	,[TypeFk] = IsNull(@TypeFk, [source].[TypeFk])
	,[StatusFk] =  @StatusFk
	,[UtilityFk] = IsNull(@UtilityFk, [source].[UtilityFk])
	,[ProjectTypeFk] = IsNull(@ProjectTypeFk, [source].[ProjectTypeFk])
	
from tb_Vendor_Projects source
where
	[source].[Vendor_ProjectId] = @OriginalVendor_ProjectId


select * from v_tb_Vendor_Projects where Vendor_ProjectId = @Vendor_ProjectId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_Project_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_Project_Delete
GO


create proc up_Project_Delete
	@Vendor_ProjectId int
as
	/* 	Initial Generated Date:  Sunday, February 08, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_Project_Test @Vendor_ProjectId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Vendor_ProjectId)
	raiserror(505060, 16, 1, 'Project', @x)
	return
end

-- proceed
delete from tb_Vendor_Projects 
where [Vendor_ProjectId] = @Vendor_ProjectId

GO


